﻿

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using System.Web;

/// <summary>
/// ExportToExcel 的摘要说明
/// </summary>
public class ExportToExcel
{
   
    public static string datatableToExcel(DataTable dt, string strExcelFileName, string[] rowNameList)
    {
        #region---第一版导出EXCEL文件代码----
        //建立空白工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();//工作簿实例
                                                   //在工作簿中：建立空白工作表
        ISheet sheet = workbook.CreateSheet(strExcelFileName);
        //在工作表1中：建立行，参数为行号，从0计
        IRow row = sheet.CreateRow(0);

        //表头样式
        ICellStyle HeadercellStyle = workbook.CreateCellStyle();
        HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

        NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
     
        headerfont.Boldweight = (short)FontBoldWeight.Bold;
        HeadercellStyle.SetFont(headerfont);

        //设置第一行表头
        int icolIndex = 0;
        IRow headerRow = sheet.CreateRow(0);
        foreach (string rowName in rowNameList)
        {
            if (icolIndex < rowNameList.Length)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(rowName);
                cell.CellStyle = HeadercellStyle;
                icolIndex++;
            }
        }
        #endregion

        //内容行样式
        ICellStyle cellStyle = workbook.CreateCellStyle();
        cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        //自动换行
        cellStyle.WrapText = true;
        NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
        //设置居中
 
        cellStyle.VerticalAlignment = VerticalAlignment.Center;
        cellStyle.Alignment = HorizontalAlignment.Center;
        cellfont.FontName = "微软雅黑";
        //cellfont.Boldweight = (short)FontBoldWeight.Bold;
        cellfont.FontHeightInPoints = 10;
        cellStyle.SetFont(cellfont);
        //设置单元格格式
        IDataFormat format = workbook.CreateDataFormat();
        cellStyle.DataFormat = format.GetFormat("0");

        //调色板
        HSSFPalette palette = workbook.GetCustomPalette();
        palette.SetColorAtIndex(8, 184, 204, 228);

        //建立内容行
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow DataRow1 = sheet.CreateRow(i + 1);
            //14 8
            //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                                      
            for (int j = 0; j < dt.Columns.Count; j++)
            {                
                ICell cell = DataRow1.CreateCell(j);
                cell.SetCellValue(dt.Rows[i][j].ToString());
                cell.CellStyle = cellStyle;
            }
        }
        //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
        //sheet.AddMergedRegion(new CellRangeAddress(1, 17, 0, 0));
        //sheet.AddMergedRegion(new CellRangeAddress(1, 10, 1, 1));
       
        string savedName = strExcelFileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        string fileName = GetPath()+"/"+savedName;
        //message = fileName;
        //SaveFilePath(id, fileName);
        ////保存在文件流
        FileStream file = new FileStream(fileName, FileMode.OpenOrCreate);
        workbook.Write(file);
        file.Flush();
        file.Close();
        return savedName;
    }

    private static string GetPath()
    {
        //string path = System.AppDomain.CurrentDomain.BaseDirectory + "/ZBWSToEXCEL";
        string path = $"wwwroot/ExportToEXCEL";
        if (!System.IO.Directory.Exists(path))
            System.IO.Directory.CreateDirectory(path);
        return path;
    }



}

